package com.cennavi.mineplay.gisfile;

import java.io.FileInputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Scanner;

import org.locationtech.jts.geom.Geometry;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

public class GeojsonReader {

	private List<Map<String, Object>> columns = null;
	private Map<String, String> tabColumnType = null;
	private List<JSONObject> geoms = null;
	private String createSql;
	private Connection conn;
	private String tableName;

	public GeojsonReader(String geojsonPath, String tableName, Connection conn)
			throws Exception {
		columns = new ArrayList<>();
		tabColumnType = new HashMap<>();
		geoms = new ArrayList<>();
		this.conn = conn;
		this.tableName = tableName;
		Scanner scanner = new Scanner(new FileInputStream(geojsonPath));

		StringBuilder sb = new StringBuilder();

		while (scanner.hasNextLine()) {
			sb.append(scanner.nextLine());
		}
		scanner.close();

		JSONObject geojson = JSONObject.parseObject(sb.toString());

		JSONArray jarray = geojson.getJSONArray("features");

		for (int i = 0; i < jarray.size(); i++) {
			JSONObject json = jarray.getJSONObject(i);

			JSONObject propJson = json.getJSONObject("properties");

			JSONObject geomJson = json.getJSONObject("geometry");

			geoms.add(geomJson);

			columns.add(propJson.getInnerMap());
		}

		Map<String, Object> detectValue = columns.get(0);

		Iterator<Map.Entry<String, Object>> it = detectValue.entrySet()
				.iterator();

		while (it.hasNext()) {
			Entry<String, Object> en = it.next();

			String columnName = en.getKey();

			Object ov = en.getValue();

			if (!(ov instanceof Geometry)) {

				if (ov instanceof Integer)
					tabColumnType.put(columnName, "int");
				else if (ov instanceof Long)
					tabColumnType.put(columnName, "int");
				else if (ov instanceof Double)
					tabColumnType.put(columnName, "numeric");
				else if (ov instanceof Float)
					tabColumnType.put(columnName, "numeric");
				else if (ov instanceof BigDecimal)
					tabColumnType.put(columnName,"numeric-big");
				else
					tabColumnType.put(columnName, "character varying(1000)");
			}

		}
		tabColumnType.put("geom", "geometry");

		StringBuilder sqlSB = new StringBuilder("create table " + tableName
				+ " (");

		Iterator<Entry<String, String>> it2 = tabColumnType.entrySet()
				.iterator();
		boolean isFirst = true;
		while (it2.hasNext()) {
			if (isFirst) {
				isFirst = false;
			} else {
				sqlSB.append(",");
			}
			Entry<String, String> en = it2.next();
			sqlSB.append(en.getKey());
			sqlSB.append(" ");
			
			String value = en.getValue();
			
			if ("numeric-big".equals(value)){
				sqlSB.append("numeric");
			}else{
				sqlSB.append(en.getValue());
			}
			
			

		}

		sqlSB.append(")");

		createSql = sqlSB.toString();

	}

	public String getCreateSql() {
		return createSql;
	}

	public void createTable() throws Exception {
		Statement stmt = conn.createStatement();

		stmt.execute(this.getCreateSql());

		stmt.close();
	}

	public void batchInsert() throws Exception {

		StringBuilder sqlSB = new StringBuilder("insert into " + tableName
				+ "(");

		String[] keys = new String[tabColumnType.size()];

		Iterator<Entry<String, String>> it = tabColumnType.entrySet()
				.iterator();
		boolean isFirst = true;

		String values = "";

		int idx = 0;

		while (it.hasNext()) {
			if (isFirst) {
				isFirst = false;
			} else {
				sqlSB.append(",");
				values += ",";
			}
			Entry<String, String> en = it.next();
			sqlSB.append(en.getKey());

			keys[idx++] = en.getKey();

			if ("geom".equals(en.getKey())) {

				values += "st_setsrid(st_geomfromgeojson(?),4326)";

			} else {

				values += "?";
			}
		}

		sqlSB.append(") values (");

		sqlSB.append(values);

		sqlSB.append(")");

		PreparedStatement pstmt = conn.prepareStatement(sqlSB.toString());

		for (int j = 0; j < columns.size(); j++) {

			Map<String, Object> mapValue = columns.get(j);

			for (int i = 0; i < keys.length; i++) {

				String key = keys[i];

				Object value = mapValue.get(key);

				String type = tabColumnType.get(key);

				if (type.equals("int")) {
					pstmt.setLong(i + 1, Long.parseLong(String.valueOf(value)));
				} else if (type.equals("numeric")) {
					pstmt.setDouble(i + 1, (Double) value);
				} else if (type.equals("numeric-big")){
					pstmt.setDouble(i+1, ((BigDecimal)value).doubleValue());
				}
				else if (type.startsWith("character")) {
					if (value != null) {
						String strValue = value.toString();
						if (strValue.length() > 300)
							pstmt.setString(i + 1, strValue.substring(0, 300));
						else
							pstmt.setString(i + 1, strValue);
					} else {
						pstmt.setString(i + 1, null);
					}
				} else {
					pstmt.setString(i + 1, geoms.get(j).toString());
				}

			}

			pstmt.addBatch();

		}

		pstmt.executeBatch();

		pstmt.close();

	}
	
	
//	public static void main(String[] args) throws Exception{
//		
//		Class.forName("org.postgresql.Driver");
//		
//		Connection conn = DriverManager.getConnection("jdbc:postgresql://36.111.84.170:5442/mineplay", "postgres", "cennavi2018");
////		
////		GeojsonReader reader = new GeojsonReader("C:/Users/lilei3774/Documents/WeChat Files/whatever_lilei/Files/particleEffect_geojson.txt", "test_geojson", conn);
////		
////		reader.createTable();
////		
////		reader.batchInsert();
//		
//		
//		
//		boolean flag = true;
//		
//		int link_seq = 0;
//		
//		String sql = "insert into lizi  (id,geom,link_seq,status) values (?,st_geomfromtext(?,4326),?,1)";
//		
//		PreparedStatement pstmt = conn.prepareStatement(sql);
//		
//		
//		double lat = 39;
//		for(double lng = 116;lng<117;lng = lng + 0.01){
//			
//				
//				StringBuilder sb = new StringBuilder("LINESTRING(");
//				 
//				sb.append(lng);
//				sb.append(" ");
//				sb.append(lat);
//				sb.append(",");
//				sb.append(lng + 0.01);
//				sb.append(" ");
//				sb.append(lat);
//				sb.append(",");
//				sb.append(lng + 0.02);
//				sb.append(" ");
//				sb.append(lat);
//				sb.append(",");
//				sb.append(lng + 0.03);
//				sb.append(" ");
//				sb.append(lat);
//				
//				sb.append(")");
//				
//				pstmt.setInt(1, link_seq);
//				pstmt.setString(2, sb.toString());
//				pstmt.setInt(3, link_seq);
//				link_seq++;
//				pstmt.addBatch();
//			}
//		
//		pstmt.executeBatch();
//		}
//		
//		
//	
		
		 

}
